package com.timeDeal.service;


import com.alibaba.druid.util.StringUtils;
import com.timeDeal.bean.PageList;
import com.timeDeal.bean.ShijianchushouBean;
import com.timeDeal.bean.XuqiufabuBean;
import com.timeDeal.util.DButil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by 叼爸爸 on 2016/9/4.
 */
public class ShijianchusouService {
    public boolean addAXuqiu(XuqiufabuBean xfb) {
        Connection conn = DButil.getConnection();
        String sql = "insert into xuqiufabu" +
                "(xuqiufabuid,miaoshu,yunxujingbiao,wanchengshijian,zhuangtai,jingbiaoriqi,xuqiumingcheng)" +
                " values(?,?,?,?,?,?,?)";
        PreparedStatement ps = null;
        ResultSet rs = null;
        XuqiufabuBean xb1 = xfb;
        try {
            ps = conn.prepareStatement(sql);
            xb1.setXuqiufabuid(DButil.getZhuJianMax("xuqiufabuid", "xuqiufabu"));
            ps.setInt(1, xb1.getXuqiufabuid());
            ps.setString(2, xb1.getMiaoshu());
            ps.setString(3, xb1.getYunxujingbiao());
            ps.setDate(4, (Date) xb1.getWanchengshijian());
            ps.setString(5, "待审核");
            ps.setDate(6, (Date) xb1.getJingbiaoriqi());
            ps.setString(7, xb1.getXuqiumingcheng());
            ps.execute();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (rs != null) {
                    rs.close();
                }
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    public ShijianchushouBean getAShijianChushouByName(String name) {
        Connection mycon = DButil.getConnection();
        String mysql = "select  *  from shijianchushou  where mingcheng=?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        ShijianchushouBean scb = new ShijianchushouBean();
        try {
            ps = mycon.prepareStatement(mysql);
            ps.setString(1, name);
            rs = ps.executeQuery();
            if (rs.next()) {
                scb.setBeizhu(rs.getString("beizhu"));
                scb.setJiafangid(rs.getInt("jiafangid"));
                scb.setShijianchushouid(rs.getInt("shijianchushouid"));
                scb.setYunxujingbiao(rs.getString("yunxujingbiao"));
                return scb;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    public ShijianchushouBean getAShijianChushouById(int id) {
        Connection mycon = DButil.getConnection();
        String mysql = "select  *  from shijianchushou  where shijianchushouid=?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        ShijianchushouBean scb = new ShijianchushouBean();
        try {
            ps = mycon.prepareStatement(mysql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                scb.setBeizhu(rs.getString("beizhu"));
                scb.setChoulao(rs.getInt("choulao"));
                scb.setJiafangid(rs.getInt("jiafangid"));
                scb.setShijianchushouid(rs.getInt("shijianchushouid"));
                scb.setYunxujingbiao(rs.getString("yunxujingbiao"));
                scb.setShijianchushoumingcheng(rs.getString("mingcheng"));
                scb.setQuyu(rs.getString("quyu"));
                scb.setBeizhu(rs.getString("beizhu"));
                scb.setZhuangtai(rs.getString("zhuangtai"));
                scb.setKaishiriqi(rs.getDate("kaishiriqi"));
                scb.setJieshuriqi(rs.getDate("jieshuriqi"));
                scb.setJineng(rs.getString("jineng"));
                return scb;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据状态得到需求用于审核
     *
     * @param zhuangtai 状态 ： 待审核，竞标中，交易中，交易完成
     * @param cu        当前页码
     * @return 需求list
     */
    public List<ShijianchushouBean> getAllXuqiuByZhuangtai(String zhuangtai, int cu) {
        Connection cn = DButil.getConnection();
        String mysql = "select  *   from  shijianchushou   where  zhuangtai=?  limit  " + (cu * 3) + "," + 3;
        PreparedStatement ps = null;
        UserService us = new UserService();
        ResultSet rs = null;
        List<ShijianchushouBean> xfb = new ArrayList<>();
        try {
            ps = cn.prepareStatement(mysql);
            ps.setString(1, zhuangtai);
            rs = ps.executeQuery();
            while (rs.next()) {
                ShijianchushouBean xb = new ShijianchushouBean();
                xb.setShijianchushoumingcheng(rs.getString("mingcheng"));
                xb.setShijianchushouid(rs.getInt("shijianchushouid"));
                xb.setBeizhu(rs.getString("beizhu"));
                xb.setTijiaorenXingming(us.getDetailById(Integer.valueOf(rs.getInt("jiafangid"))));
                xfb.add(xb);
            }
            return xfb;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (rs != null) {
                    rs.close();
                }
                cn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 根据状态得到待审核需求数量
     *
     * @param zhuangtai 状态 ： 待审核，竞标中，交易中，交易完成
     * @return 数量
     */
    public String getCountByZhuangtai(String zhuangtai) {
        Connection cn = DButil.getConnection();
        String mysql = "select count( *)   from  shijianchushou   where zhuangtai=?  ";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = cn.prepareStatement(mysql);
            ps.setString(1, zhuangtai);
            rs = ps.executeQuery();
            if (rs.next()) {
            }
            return rs.getString(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (rs != null) {
                    rs.close();
                }
                cn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return "";
    }
    public PageList getShijianByTiaojian(int cu , String tiaojian){
        Connection connection = DButil.getConnection();
        UserService userService = new UserService() ;
        String mysql="select count( *)   from  shijianchushou   where zhuangtai=?  "   ;
        String mysqlyunxu="select count( *)   from  shijianchushou   where zhuangtai=?  and yunxujingbiao =? "   ;
        int pagesize = PageList.pagesize ;
        String mysqlleft="select *   from  shijianchushou   where zhuangtai=?   "  ;
        String  mysqldown   =  "  DESC   limit   "+ (cu*pagesize-pagesize)+", "+pagesize   ;
        String  mysqljingbiao  =  "select *   from  shijianchushou   where zhuangtai=?  and  yunxujingbiao=? " ;
        String order = "   ORDER BY   " ;
        PageList  pageList =  new PageList() ;
        List<Object> list =new ArrayList<>() ;
        PreparedStatement ps=null;
        ResultSet rs=null;
        if(StringUtils.equals(tiaojian,"热度")){
            mysqlleft = mysqlleft +order+ "redu" + mysqldown ;
        }
        if(StringUtils.equals(tiaojian,"发布时间")){
            mysqlleft = mysqlleft  +order+ "fabushijian" + mysqldown ;
        }
        if(StringUtils.equals(tiaojian,"酬劳金额")){
            mysqlleft = mysqlleft  +order+ "choulao" + mysqldown ;
        }
        try{
            if(StringUtils.equals(tiaojian,"允许竞标")){
                ps=connection.prepareStatement(mysqlyunxu);
                ps.setString(2,"是");
            }
            else if(StringUtils.equals(tiaojian,"不允许竞标")){
                ps=connection.prepareStatement(mysqlyunxu);
                ps.setString(2,"否");
            }
            else{ ps=connection.prepareStatement(mysql);}
            ps.setString(1,"审核通过");
            rs = ps.executeQuery();
            if (rs.next()){
            }
            pageList.setTotalpage(rs.getInt(1)%pagesize==0?rs.getInt(1)/pagesize:rs.getInt(1)/pagesize+1) ;
            pageList.setCurrentpage(cu);
            ps.clearParameters();
            if(StringUtils.equals(tiaojian,"允许竞标")){
                mysqlleft = mysqljingbiao  +order+ "fabushijian" + mysqldown ;
                ps=connection.prepareStatement(mysqlleft);
                ps.setString(2,"是");
            }
            else if(StringUtils.equals(tiaojian,"不允许竞标")){
                mysqlleft = mysqljingbiao   + order+"fabushijian"+  mysqldown  ;
                ps=connection.prepareStatement(mysqlleft);
                ps.setString(2,"否");
            }
            else {  ps=connection.prepareStatement(mysqlleft);}
            System.out.println(mysqlleft);
            ps.setString(1,"审核通过");
            rs=ps.executeQuery() ;
            while (rs.next()){
                ShijianchushouBean shijianchushouBean = new ShijianchushouBean() ;
                if(StringUtils.equals(tiaojian,"允许竞标")&&rs.getString("yunxujingbiao")=="否"){
                    continue;
                }
                if(StringUtils.equals(tiaojian,"不允许竞标")&&rs.getString("yunxujingbiao")=="是"){
                    continue;
                }
                shijianchushouBean.setShijianchushoumingcheng(rs.getString("mingcheng"));
                shijianchushouBean.setShijianchushouid(rs.getInt("shijianchushouid"));
                shijianchushouBean.setRedu(rs.getInt("redu"));
                shijianchushouBean.setJiafangid(rs.getInt("jiafangid"));
                shijianchushouBean.setTijiaorenXingming(userService.getDetailById(rs.getInt("jiafangid")));
                shijianchushouBean.setFabushijian(rs.getDate("fabushijian"));
                shijianchushouBean.setChoulao(rs.getInt("choulao"));
                shijianchushouBean.setYunxujingbiao(rs.getString("yunxujingbiao"));
                shijianchushouBean.setChoulaoxiaxian(rs.getInt("choulaoxiaxian"));
                list.add(shijianchushouBean);
            }
            pageList.setList(list);
            return  pageList ;
        }
        catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  null ;
    }
    /**
     * 根据状态得到待审核需求数量
     * @param id   id
     * @return    数量
     */
    public   String  getCountByJIafangid(int id){
        Connection cn = DButil.getConnection();
        String mysql="select count( *)   from  shijianchushou   where zhuangtai=? and jiafangid=?  "   ;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            ps=cn.prepareStatement(mysql);
            ps.setString(1,"交易完成");
            ps.setInt(2,id);
            rs = ps.executeQuery();
            if (rs.next()){
            }
            return  rs.getString(1);
        }
        catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                cn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  "" ;
    }
    public boolean zengJiaRedu(int id) {
        Connection conn = DButil.getConnection();
        String mysql = "update shijianchushou set redu=ifnull(redu,0)+1 where shijianchushouid=?";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(mysql);
            ps.setInt(1, id);
            ps.execute();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
}
